Epilogue in MSSQL ?

[recognition]

Hi,
In the revisions in GameServer\Config\server.properties exist this command.

# Database URL

# URL = jdbc:mysql://localhost/l2jdb (default)

# URL = jdbc:hsqldb:hsql://localhost/l2jdb

# URL = jdbc:sqlserver://localhost/database = l2jdb/user = sa/password =

URL = jdbc:mysql://localhost/l2jdb

If I choose the option # URL = jdbc:sqlserver://localhost/database = l2jdb/user = sa/password = , Would be possible conect with MSSQL by Microsoft?

Excuse-me the errors.

[kilkenny]

nope, the whole database and all the in server query's are based on MySQL. You would need to rewrite and redo everything for L2J to work on MSSQL

[janiii]

you could try it. imo most of the sql is not mysql specific, but there may be some.

you can check this article:
http://mysoftskill.blogspot.com/2009/11 ... nd-my.html
or this:
http://troels.arvin.dk/db/rdbms/

i think l2j uses sometimes the limit function of mysql (e.g. top 10 hero).

[noob256]

we are currently underway with that mssql db migration too and will see how this works out.
the links previously posted seem a good start already.

if you want to get started a good idea to read up on the migration might be this link (describing migration to mssql 2000 but 2005/2008 should be similar)
http://technet.microsoft.com/en-us/libr ... 66396.aspx

as for lazy people this tool seems to do the trick -on the fly db migration oO:
http://www.microsoft.com/downloads/deta ... laylang=en
i havent used it yet, but i assume it might just convert all the unsigned numeric data types to the next larger signed mssql datatype. but it appears that in most cases you can use the normal (signed) mssql data type instead to save your server some memory load in scenarios of high demand.
still as mentioned before you will have to take a look at l2j data layer either way. well just getting started with this..

oh and i guess the choice of the correct jdbc driver will matter too. there is one available from microsoft but this project looks promising too:
http://jtds.sourceforge.net/index.html
maybe someone else has already collected additonal expertise in this?

[noob256]

it worked pretty well with the ms tool.

you need the mysql odbc connector for your mysql edition (32 or 64bits)
http://dev.mysql.com/downloads/connector/odbc/5.1.html

the tool itself as linked before
http://www.microsoft.com/downloads/deta ... laylang=en
and ofc mssql running.

everything is pretty much self- explanatory. you can define default mappings of mysql datatypes which mssql doesnt support (such as unsigned numerics). you will get a full conversion with existing data content. in case existing data inside a field is too big for the default mapped target data type you will recieve a warning. piece of cake.
http://img688.imageshack.us/img688/9940/unbenanntah.jpg

next step would be still to review your field data types. some might be too big, some might eventually run too short one day.
http://img263.imageshack.us/img263/2322/unbenannthc.jpg

after that it s up for l2js datalayer. yet starting over with that step right now..
one approach seems to simply search the code for non-standard mysql commands. might come back later with info in case someone is not interested in this.

[jurchiks]

it's interesting, but useful mostly for highly populated servers, since mssql costs money and is more complex than mysql

[recognition]

Thanks for the informations friends.

Any future project team L2jServer to other databases?

Thanks for the informations.

Recognition

[noob256]

i don t know about other projects to use alternative db providers such as oracle. but fact is it still works, besides the info explained above you need to migrate L2J´s data layer to MSSQL as mentioned.
reason is that some MYSQL commands are not supported by MSSQL. for example the "REPLACE" statement.
i.e. in order to use an "INSERT" you will have to perform a check with an IF condition and so on. you can find a complete comparison of mysql vs mssql commands on the web. the migration itself is like a weekends work, depending on how often you did it already *cough*..

one key issue you must evaluate before are however updates.
at least now since l2j team changed their policy which is very very annoying from this point of view (before we used to import them 1by1. now we ll probably rely on another l2j provider with public svn :s).

however the server works like a charm on MSSQL. it´s awesome.

[MELERIX]

Any future project team L2jServer to other databases?

for what ? Oo

[n0name12]

i dont see why someone would want to pay for mssql license to install l2j server.

[Probe]

mssql isn't the only other option.. postreSQL is also a free db

[Phantom2005]

PostgreSQL was even more annoying
Everything has to be perfect: case sentivity etc. correct ', ` or [ ] (forgot which one it was for PostgreSQL).

I'm in for making L2J more compatible with other database engines.
If forums software(phpbb, etc.) can do it.. why can't we?

MSSQL doesn't always cost money, for example if you use the Express Edition which works quite well.

[Ralm]

Was any improvement now at Freya release about this question??

What are the great improvements about using MsSQL instead of MySql???


Thanks,
Ralm.

P.S.: My first post XD

[noob256]

hi,
well have not tried this with freya, the last l2j based release we made running on mssql was the final gracia epilogue release. the "advantages" are first off a matter of personal preference.
for me (us) they were:

• being used to work with mssql (can be a huge advantage you know), overall usability and maintenance/ features but again matter of preference i guess
• better scalability for high connection count, hardware is cheap nowadays so a solution optimized for a high amount of transactions seemed preferable, even if it initially generates a bigger overhead
• more headroom for custom features that might lay further stress on the db
• could be more but i m being distracted atm, those were the essential reasons for us i recall
  overall: if you are not used to work with mssql and/ or you do not estimate to serve thousands of players and/or build db stress intensive customizations of epic magnitude plus if you intend to use l2j updates (since no public svn anymore) then in any case the standard mysql setup is the preferred solution.

as for performance i can only give very rough probably flawed estimations based on npc spawn since the server has never hit a larger playerbase yet. however we could spawn a way higher number of npcs in a single area before performance issues kicked in. now bear in mind that we were running a windows machine and didnt optimize any mysql config so this is flawed yet db lag set in at about aoe ing 1k mobs while on mssql we went past 5k mobs on the same system. but this is no real comparison (so don t sue me for these lines).

as you asked me for the connection string in the properties config files for login and gameserver to be used with the microsoft jdbc driver:
Driver = com.microsoft.sqlserver.jdbc.SQLServerDriver
URL = jdbc:sqlserver://<ip>:<port>;Databasename = <dbname>