SQL Server Performance

32 bit to 64 bit machine

Discussion in 'SQL Server 2005 General DBA Questions' started by priyaram, May 18, 2007.

  1. priyaram New Member

    We are moving our databases along with SAN from 32 bit machines of sqlserver 2005 to the 64 bit server of sqlserver 2005.

    is there a way we can move master database to the new 64 bit machine, so that there is no need to transfer any logins.And nothing will be missed.

    but in the new machine once i installed the sqlserver 2005 the master database got created. So how can i move the master database from old to new server if one is already existing in the new server
  2. satya Moderator

    I believe you have relevant posts answered here about moving databases from one instance to another, in the case of 32 to 64 bit is no change.

    KBAhttp://support.microsoft.com/kb/224071 fyi in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. priyaram New Member

    how can is the time we can delay between attaching and detaching databases.

  4. satya Moderator

    Have you read the article above?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. priyaram New Member

    hi sathya,

    this article describes the process for NAS , but we are using SAN.Will the same steps it be applicable for SAN too
  6. Raulie New Member

  7. MohammedU New Member

    There is no special directions to move the databases on SAN...
    You just following the instructions (backup restore or detach/attach ) from the article Satya mentioned ....

    It is just simple database move because sql server dbs can be move from 32 to 64 bit and vice versa without any issues...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  8. priyaram New Member

    Before moving the databases , i planned to move jobs,maintanence plans,alters etc..
    then after moving databases , we are planning to rename the server..
    so after renaming the server will jobs... and everything will work , or we need to change some thing else....


    thanks
  9. satya Moderator

    Nothing I can see and for the scheduled job unless you have hardcoded the servername, that implies to DTS packages and any other processess.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  10. Raulie New Member

    Did you also rename the server in SQL Server or just OS level?

    Raulie



  11. MohammedU New Member

    quote:Originally posted by priyaram

    Before moving the databases , i planned to move jobs,maintanence plans,alters etc..
    then after moving databases , we are planning to rename the server..
    so after renaming the server will jobs... and everything will work , or we need to change some thing else....


    thanks
    Everything should work without any issues...but sometimes if you restore the msdbd to move the jobs you can't modify them, you need to update the servername in sysjobs but I have not tested this in sql server 2005.


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  12. satya Moderator

    You cannot modify the system tables explicitly in SQL 2005 and only possible http://sqlserver-qa.net/blogs/tools...ates-to-system-catalogs-are-not-allowed.aspx] using this.

    I wouldn't testif updating the system tables in this case, take proper route of system databases movement.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  13. priyaram New Member

    i am having 10 user ddatabases to move to the new server.
    so i have to detach one by one then attach one by one??


    use master
    go
    sp_detach_db 'mydb'
    go

    use master
    go
    sp_detach_db 'mydb1'
    go
    use master
    go
    sp_detach_db 'mydb2'
    go


    then i have to got to the target server , then attach one by one

    use master
    go
    sp_attach_db 'mydb','E:Sqldatamydbdata.mdf','E:Sqldatamydblog.ldf'
    go

    use master
    go
    sp_attach_db 'mydb1','E:Sqldatamydbdata1.mdf','E:Sqldatamydblog1.ldf'
    go
    use master
    go
    sp_attach_db 'mydb2','E:Sqldatamydbdata2.mdf','E:Sqldatamydblog2.ldf'
    go
  14. MohammedU New Member

    Yes...
    You can detach all at once or one by one and copy the files and attach them using the proper file name and location or you can use SSMS...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  15. priyaram New Member

    once rename the server , then we move databases and everything to 64 bit will this server renaming will effect the sqlserver 2005 anyways...


    (will it need to be reinstalled.. after changing the server name....)
  16. satya Moderator

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=21968

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  17. MohammedU New Member

    You may need to flush the DNS entries if you had another server with the same name in the network...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  18. satya Moderator

    .... using the following method:
    Start -> Run -> type cmd
    - in command prompt, type ipconfig /flushdns

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  19. priyaram New Member

    i need to run this command before renaming the server or after moving the databases and renaming the server ??
  20. MohammedU New Member

    After renaming the server...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  21. priyaram New Member

    Hi mohammed,

    in the first server (old server) - i am loggin in as Test1 account and detaching the databases

    in the second server (new server) - i am loggin in as sa account and attaching the databases,
    so now who will be owner of the databses after moving to the new server
    test1 or sa
  22. satya Moderator

    If the login is a part of SYSADMIN, then it will be SA.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page